package com.hxgis.statisticanalysis.utils;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;

import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.List;

/**
 * 导出Excel工具类
 * @Author: 陈敬琪
 * @Date: 2021/1/8 11:07
 */
public class ExcelUtil {

    public static void resolve(XSSFWorkbook workBook, Object object,int maxCellNum) throws Exception{
        Field[] fields = object.getClass().getDeclaredFields();
        HashMap<String,Object> fieldsMap = new HashMap<>();
        for (Field field : fields) {
            fieldsMap.put("{"+field.getName()+"}", MethodUtil.getGetMethod(object,field.getName()));
        }
        for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
            String sheetName = workBook.getSheetName(i);
            if (fieldsMap.containsKey(sheetName)){
                workBook.setSheetName(i,fieldsMap.get(sheetName).toString());
            }
            XSSFSheet sheetAt = workBook.getSheetAt(i);
            for (int j = 0; j <= sheetAt.getLastRowNum(); j++) {
                XSSFRow row = sheetAt.getRow(j);
                //存在无数据的行
                if (row == null){
                    continue;
                }
                for (int k = 0; k <= row.getLastCellNum(); k++) {
                    XSSFCell cell = row.getCell(k);
                    //存在无数据的列
                    if (cell == null){
                        continue;
                    }
                    String rawValue = cell.getStringCellValue();
                    if (fieldsMap.containsKey(rawValue)){
                        Object val = fieldsMap.get(rawValue);
                        //针对表格行
                        if (val instanceof List){
                            List list = (List) val;
                            for (int i1 = 0; i1 < list.size(); i1++) {
                                XSSFRow row1 = sheetAt.getRow(j+i1);
                                if (row1 == null ){
                                    row1 = sheetAt.createRow(j+i1);
                                }
                                resolveRow(list.get(i1),row1,0,maxCellNum);
                            }
                            j += list.size()-1;
                            break;
                        }else {
                            cell.setCellValue(val.toString());
                        }
                    }else {
                        for (String name : fieldsMap.keySet()) {
                            String val = fieldsMap.get(name).toString();
                            if (rawValue.contains(name)){
                                rawValue = rawValue.replace(name,val);
                            }
                        }
                        cell.setCellValue(rawValue);
                    }
                }
            }
        }
    }

    private static int resolveRow(Object obj, XSSFRow row,Integer cellNum,int maxCellNum) throws Exception{
        //如果是基本类型，直接赋值，目前只考虑字符串类型
        if (obj instanceof String){
            XSSFCell cell1 = row.getCell(cellNum);
            if (cell1 == null){
                cell1 = row.createCell(cellNum);
            }
            cell1.setCellValue(obj.toString());
            cellNum++;
            return cellNum;
        }
        //只考虑一列中有一个嵌套的list
        for (int k1 = 0; k1 < obj.getClass().getDeclaredFields().length; k1++) {
            Field field = obj.getClass().getDeclaredFields()[k1];
            if (field.getModifiers() != 2){
                continue;
            }
            if (field.getType() == List.class){
                for (int i = 0; i < ((List) MethodUtil.getGetMethod(obj,field.getName())).size(); i++) {
                    cellNum = resolveRow(((List) MethodUtil.getGetMethod(obj,field.getName())).get(i),row,cellNum,maxCellNum);
                }
            }else {
                //最后一个
                if (k1 == obj.getClass().getDeclaredFields().length-1){
                    cellNum = maxCellNum;
                }
                XSSFCell cell1 = row.getCell(cellNum);
                if (cell1 == null){
                    cell1 = row.createCell(cellNum);
                }
                cell1.setCellValue(MethodUtil.getGetMethod(obj,field.getName()).toString());
                cellNum++;
            }
        }
        return cellNum;
    }

    /**
     * 获取excel格式
     * @param workbook
     * @param color
     * @param isBoldWeight
     * @return
     */
    public static XSSFCellStyle getCellStyle(XSSFWorkbook workbook, Short color, boolean isBoldWeight) {
        if (workbook == null) {
            return null;
        }
        XSSFCellStyle style = workbook.createCellStyle();
        if (color != null) {
            style.setFillForegroundColor(color);// 设置背景色
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }
        style.setBorderBottom(BorderStyle.THIN); // 下边框
        style.setBorderLeft(BorderStyle.THIN);// 左边框
        style.setBorderTop(BorderStyle.THIN);// 上边框
        style.setBorderRight(BorderStyle.THIN);// 右边框
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(true);//设置自动换行

        //字体设置
        XSSFFont font = workbook.createFont();
        font.setFontName("仿宋_GB2312");
        if (isBoldWeight) {
            font.setBold(true);
//            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
        }
        font.setFontHeightInPoints((short) 12); // 字体大小
        style.setFont(font);// 选择需要用到的字体格式
        return style;
    }

    /**
     * 设置边框
     * @param region
     * @param sheet
     */
    public static void setBorderStyle(CellRangeAddress region, XSSFSheet sheet) {
        RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet);//下边框
        RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet);     //左边框
        RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet);    //右边框
        RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet);      //上边框
    }
}
